-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

--Lay lu dieu bang Phu Thu
create proc sp_PhuThuGetAll
as
begin
select * from PHUTHU
end
--Lay danh sach loai khach hang
create proc sp_LoaiKhachHangGetAll
as
begin
select * from LOAIKHACHHANG
end
--Lay danh sach loai phong
create proc sp_LoaiPhongGetAll
as
begin
select * from LOAIPHONG
end
--Insert Loai Phong
create proc sp_InsertLoaiPhong
@TenLoaiPhong NVarChar(50),
@SoKhachToiDa Int,
@DonGia Float
as
begin
insert into LOAIPHONG
([TenLoaiPhong],[SoKhachToiDa],[DonGia])
Values
(@TenLoaiPhong,@SoKhachToiDa,@DonGia)
end
--Lay phong theo ten
create proc sp_LoaiPhongTheoTen
@TenLoaiPhong nvarchar(50)
as
begin
select * from LOAIPHONG where TenLoaiPhong =@TenLoaiPhong
end
-- them phu thu
create proc sp_InsertPhuThu
@SoThuTu int,
@TyLePhuThu float
as
begin
insert into PHUTHU
([SoThuTu],[TyLePhuThu])
Values(@SoThuTu,@TyLePhuThu)
end
--Cap nhat loai phong
Create proc sp_UpdateLoaiPhong
@MaLoaiPhong Int,
@TenLoaiPhong NVarChar(50),
@SoKhachToiDa Int,
@DonGia Float
as
begin
Update LOAIPHONG Set
 [TenLoaiPhong]= @TenLoaiPhong,[SoKhachToiDa]= @SoKhachToiDa,[DonGia]= @DonGia
 Where ([MaLoaiPhong]= @MaLoaiPhong)
end
--Them loai khach hang

Create proc sp_InsertLoaiKhach
@TenLoaiKhach NVarChar(50),
@HeSoLoaiKhach Float
as
begin
insert into LOAIKHACHHANG
([TenLoaiKhach],[HeSoLoaiKhach])
Values
(@TenLoaiKhach,@HeSoLoaiKhach)
end

--Update loai khach hang
Create proc sp_UpdateLoaiKhach
@MaLoaiKhach Int,
@TenLoaiKhach NVarChar(50),
@HeSoLoaiKhach Float
as
begin
Update LOAIKHACHHANG Set
 [TenLoaiKhach]= @TenLoaiKhach,[HeSoLoaiKhach]= @HeSoLoaiKhach
 Where ([MaLoaiKhach]= @MaLoaiKhach)
end

-- update phu thu
Create proc sp_UpdatePhuThu
@MaPhuThu Int,
@SoThuTu Int,
@TyLePhuThu Float
as
begin
Update PHUTHU Set
 [SoThuTu]= @SoThuTu,[TyLePhuThu]= @TyLePhuThu
 Where ([MaPhuThu]= @MaPhuThu)
end
---Lay danh sach khach hang
create proc sp_KhachHangGetAll
as
begin
select * from KHACHHANG
end
-- lay danh sach tham so
create proc sp_ThamSoGetAll
as
begin
select * from THAMSO
end
-- lay ma loai khach theo ma khach hang
create proc sp_GetTheoMa
@MaKhachHang int
as 
begin
select MaLoaiKhach from KHACHHANG where MaKhachHang=@MaKhachHang
end
--lay tu le phu thu theo stt
create proc sp_LayTyLePhuThu
@STT int
as
begin
select * from PHUTHU where SoThuTu=@STT
end
--lay he so loai khach theo ma loai khach
create proc sp_HeSoTheoMaLoaiKhach
@MaLoaiKhach int
as 
begin
select HeSoLoaiKhach From LOAIKHACHHANG Where MaLoaiKhach=@MaLoaiKhach
end
-- them phieu thue phong
Create proc sp_InsertPhieuThuePhong
@MaPhong Int,
@NgayThuePhong DateTime,
@NgayKetThuc DateTime,
@DonGia Float,
@SoNgayThue Int,
@ThanhTien Float
as
begin
insert into PHIEUTHUEPHONG
([MaPhong],[MaHoaDon],[NgayThuePhong],[NgayKetThuc],[DonGia],[SoNgayThue],[ThanhTien])
Values
(@MaPhong,null,@NgayThuePhong,@NgayKetThuc,@DonGia,@SoNgayThue,@ThanhTien)
end
--cap nhat tinh trang phong
Create proc sp_UpdateTinhTrangPhong
@MaPhong Int,
@TinhTrangPhong Int
as
begin
Update PHONG Set
[TinhTrangPhong]= @TinhTrangPhong
 Where ([MaPhong]= @MaPhong)
end
--lay danh sach chi tiet phieu thue
Create proc sp_ChiTietPhieuThueGetAll
as
begin
Select * from CHITIETPHIEUTHUE
end
--Them chi tiet phieu thue
Create proc sp_InsertChiTietPhieuThue
@MaPhieuThue Int,
@MaKhachHang Int
as
begin
insert into CHITIETPHIEUTHUE
([MaPhieuThue],[MaKhachHang])
Values
(@MaPhieuThue,@MaKhachHang)
end
--lay danh sach phieu thue phong
Create proc sp_PhieuThuePhongGetAll
as
begin
Select * from PHIEUTHUEPHONG
end 
-- lay loai phong
create proc sp_LayLoaiPhong
as
begin
select TenLoaiPhong,MaLoaiPhong from LOAIPHONG
end
-- lay phong theo ma loai phong
create proc sp_PhongTheoMaLoai
@MaLoaiPhong int
as
begin
select * from PHONG where MaLoaiPhong=@MaLoaiPhong
end
--lay ma khach hang theo ten
create proc sp_MaKhachTheoTen
@TenKhachHang NVarChar(50)
as
begin
select MaKhachHang from KHACHHANG where TenKhachHang like N'%' + @TenKhachHang + '%'
end
drop  proc sp_MaKhachTheoTen

--lay mã khach đang thue phong theo ten
create proc sp_MaTheoTen
@TenKhachHang NVarChar(50)
as
begin
select ct.MaKhachHang from KHACHHANG kh,CHITIETPHIEUTHUE ct,PHIEUTHUEPHONG pt,PHONG p where  kh.MaKhachHang=ct.MaKhachHang and ct.MaPhieuThue=pt.MaPhieuThue and pt.MaPhong=p.MaPhong and p.TinhTrangPhong=0 and kh.TenKhachHang like N'%' + @TenKhachHang + '%'
end
drop proc sp_MaTheoTen
--Insert phong
Create proc sp_InsertPhong
@TenPhong NVarChar(50),
@MaLoaiPhong Int,
@TinhTrangPhong Int,
@GhiChu NVarChar(500)
as
begin
insert into PHONG
([TenPhong],[MaLoaiPhong],[TinhTrangPhong],[GhiChu])
Values
(@TenPhong,@MaLoaiPhong,1,@GhiChu)
end
drop proc sp_InsertPhong

--Lấy danh sach phong GetAll
create proc sp_PhongGetAll
as
begin
Select p.MaPhong, p.TenPhong, lp.TenLoaiPhong, lp.DonGia, p.GhiChu, p.TinhTrangPhong, p.MaLoaiPhong From PHONG p, LOAIPHONG lp where p.MaLoaiPhong = lp.MaLoaiPhong
end
--Phong theo ten
create proc sp_PhongTheoTen
@TenPhong NVarchar(50)
as
begin
select MaPhong from PHONG where TenPhong=@TenPhong
end
drop proc sp_PhongTheoTen
